package com.orhon.smartcampus.modules.student.fetcher;


import java.io.FileNotFoundException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.fastjson.JSONObject;
import com.orhon.smartcampus.auth.JwtUtils;
import com.orhon.smartcampus.basicComponents.service.GQLRunService;
import com.orhon.smartcampus.modules.base.dao.DictionaryDao;
import com.orhon.smartcampus.modules.base.dao.SchoolsDao;
import com.orhon.smartcampus.modules.base.entity.Schools;
import com.orhon.smartcampus.modules.student.dao.HonorDao;
import com.orhon.smartcampus.modules.student.dao.RewardsDao;
import com.orhon.smartcampus.modules.student.dao.SInformationDao;
import com.orhon.smartcampus.modules.student.entity.Honor;
import com.orhon.smartcampus.modules.student.entity.Rewards;
import com.orhon.smartcampus.modules.student.entity.SInformation;
import com.orhon.smartcampus.modules.user.dao.TokenService;
import com.orhon.smartcampus.modules.user.dao.UsersDao;
import com.orhon.smartcampus.modules.user.entity.Users;
import com.orhon.smartcampus.utils.ExcelUtil;
import com.orhon.smartcampus.utils.R;


@RestController
@RequestMapping(value = "/export", produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
@CrossOrigin(origins="*")
public class StudentElxeController<E> {

	@Autowired
	private TokenService tokenService;

	@Autowired
	private UsersDao usersDao;

	@Autowired
	private SInformationDao informationDao;

	@Autowired
	private SchoolsDao schoolsDao;
	
	 @Autowired
	 private   DictionaryDao dictionaryDao;
	 @Autowired
	 private RewardsDao rewardsDao;
	 @Autowired
	 private HonorDao honorDao;

	/**
	 * 导出模板
	 * @return
	 */
	@GetMapping(value = "/export")
	public void export(HttpServletResponse response,@RequestParam HashMap<String, Object> maps) throws Exception {
		try {
			ServletOutputStream out = response.getOutputStream();	
				
			ExcelUtil eeu = new ExcelUtil();
			HSSFWorkbook workbook = new HSSFWorkbook();

			ArrayList<String> arrayList = new ArrayList<String>();
			ArrayList<String> array = new ArrayList<String>();
			arrayList.add("学校");
			arrayList.add("年级");
			arrayList.add("班级");
			arrayList.add("中文名");
			arrayList.add("蒙文名");
			arrayList.add("学籍号");
			arrayList.add("身份证号");
			arrayList.add("性别(男,女)");
			arrayList.add("民族");
			List<List<String>> data = new ArrayList<List<String>>();
			String[] array1=arrayList.toArray(new String[arrayList.size()]);
			String[] array2=array.toArray(new String[array.size()]);
			int i = 0;
			eeu.exportExcel(workbook, i,"学生详情表" , array1,array2, data, out,"学生基础信息表");
			//原理就是将所有的数据一起写入，然后再关闭输入流。
			setResponseHeader(response, "学生导入模板.xls");
			workbook.write(out);
			out.close();		
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 奖惩模板
	 * @return
	 */
	@GetMapping(value = "/rewards")
	public void rewards(HttpServletResponse response,@RequestParam HashMap<String, Object> maps) throws Exception {
		try {
			ServletOutputStream out = response.getOutputStream();	
				
			ExcelUtil eeu = new ExcelUtil();
			HSSFWorkbook workbook = new HSSFWorkbook();

			ArrayList<String> arrayList = new ArrayList<String>();
			ArrayList<String> array = new ArrayList<String>();
			arrayList.add("学生姓名");
			arrayList.add("学生学号");
			arrayList.add("纪律名称");
			arrayList.add("分数");
			arrayList.add("备注");
			
			List<List<String>> data = new ArrayList<List<String>>();
			String[] array1=arrayList.toArray(new String[arrayList.size()]);
			String[] array2=array.toArray(new String[array.size()]);
			int i = 0;
			eeu.exportExcel(workbook, i,"奖惩导入表" , array1,array2, data, out,"奖惩表");
			//原理就是将所有的数据一起写入，然后再关闭输入流。
			setResponseHeader(response, "奖惩导入模板.xls");
			workbook.write(out);
			out.close();		
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 奖惩模板
	 * @return
	 */
	@GetMapping(value = "/honor")
	public void honor(HttpServletResponse response,@RequestParam HashMap<String, Object> maps) throws Exception {
		try {
			ServletOutputStream out = response.getOutputStream();	
				
			ExcelUtil eeu = new ExcelUtil();
			HSSFWorkbook workbook = new HSSFWorkbook();

			ArrayList<String> arrayList = new ArrayList<String>();
			ArrayList<String> array = new ArrayList<String>();
			arrayList.add("学生姓名");
			arrayList.add("学生学号");
			arrayList.add("考试名称");
			arrayList.add("获奖时间");
			arrayList.add("名次");
			arrayList.add("等级");
			arrayList.add("颁发单位");
			arrayList.add("备注");
			
			List<List<String>> data = new ArrayList<List<String>>();
			String[] array1=arrayList.toArray(new String[arrayList.size()]);
			String[] array2=array.toArray(new String[array.size()]);
			int i = 0;
			eeu.exportExcel(workbook, i,"荣誉导入表" , array1,array2, data, out,"荣誉表");
			//原理就是将所有的数据一起写入，然后再关闭输入流。
			setResponseHeader(response, "荣誉导入模板.xls");
			workbook.write(out);
			out.close();		
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	//发送响应流方法
	public void setResponseHeader(HttpServletResponse response, String fileName) {
		try {
			try {
				fileName = new String(fileName.getBytes(),"ISO8859-1");
			} catch (UnsupportedEncodingException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			response.setContentType("application/octet-stream;charset=ISO8859-1");
			response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
			response.addHeader("Pargam", "no-cache");
			response.addHeader("Cache-Control", "no-cache");
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	/**
	 * 导入
	 * @param request
	 * @param model
	 * @return@RequestParam(value = "logo", required = true)
	 * @throws Exception 
	 * @throws FileNotFoundException r
	 */
	@Transactional(readOnly = false)//
	@RequestMapping( value = "/toLead", method = RequestMethod.POST)
	public R xlsx(@RequestParam("file") MultipartFile file) throws Exception {
		if(file.isEmpty()!=true){
			String token = tokenService.getSystemToken();
			Long school_id = JwtUtils.getSchoolId(token);
			Schools schools = schoolsDao.findById(school_id);
			String hql = "from Dictionary where deleted_at = null ";

			if(schools==null) {
				return R.error();
			}

			InputStream stream = file.getInputStream();
			try {
				HSSFWorkbook hssfWorkbook = new HSSFWorkbook(stream);
				// 循环工作表Sheet         
				for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
					HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
					if (hssfSheet == null) {
						continue;
					}

					HSSFRow hssfRow = hssfSheet.getRow(1);
					ArrayList<String> list = new ArrayList<String>();
					int j=0;
					for (Cell cell : hssfRow) {
						String value = getValue(hssfRow.getCell(j));
						list.add(value);
						j++;
					}

					// 循环行Row
					for (int rowNum = 2; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
						HSSFRow hssfRow1 = hssfSheet.getRow(rowNum);
						if (hssfRow1 != null) {
							Users users = new Users();
							users.setIdCard(getValue(hssfRow1.getCell(6)));
							users.setUsername(getValue(hssfRow1.getCell(3)));
							users.setPassword("$2y$10$fe8uwZzf6Qdr5rX9WQ7NauLjQz6Fn5EBuMH60720aomWCvxtDx7ii");
							users.setSchool(schools);
							users.setUser_type("student");
							try {
								usersDao.add(users);
								SInformation information = new SInformation();
								JSONObject object = new JSONObject();
								object.put("mn", getValue(hssfRow1.getCell(3)));
								object.put("zh", getValue(hssfRow1.getCell(4)));
								information.setUser(users);
								information.setStudent_name(object.toJSONString());
								information.setIdcard(getValue(hssfRow1.getCell(6)));
								information.setStudent_number(getValue(hssfRow1.getCell(5)));
								if(getValue(hssfRow1.getCell(7)).equals("男")) {
									information.setSex("gender_1");
								}else if(getValue(hssfRow1.getCell(7)).equals("女")){
									information.setSex("gender_2");
								}else {
									information.setSex("gender_9");
								}
						        if(!getValue(hssfRow1.getCell(8)).equals("")) {
						        	HashMap<String,String> hashMap = GQLRunService.DictionaryNameMap();
						        	String value = getValue(hssfRow1.getCell(8));
						        	String string = GQLRunService.DictionaryNameMap().get(getValue(hssfRow1.getCell(8)));
						        	information.setNation(string);
						        }
								
								information.setSchool(schools);
								informationDao.save(information);
							} catch (Exception e) {
								R.error();
							}
						}
					}
				}        
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			}
			return R.ok().put("data", "导入成功");
		}
		return R.error("文件不存在!");
	}

	
	/**
	 * 奖惩导入
	 * @param request
	 */
	@Transactional(readOnly = false)//
	@RequestMapping( value = "/rewardsToLead", method = RequestMethod.POST)
	public R rewardsToLead(@RequestParam("file") MultipartFile file) throws Exception {
		if(file.isEmpty()!=true){
			String token = tokenService.getSystemToken();
			Long school_id = JwtUtils.getSchoolId(token);
			String username = JwtUtils.getUsername(token);
			Schools schools = schoolsDao.findById(school_id);
			
			
			if(schools==null) {
				return R.error();
			}
			ArrayList<String> arrayList = new ArrayList<String>();
			InputStream stream = file.getInputStream();
			try {
				HSSFWorkbook hssfWorkbook = new HSSFWorkbook(stream);
				// 循环工作表Sheet         
				for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
					HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
					if (hssfSheet == null) {
						continue;
					}
					
					// 循环行Row
					for (int rowNum = 2; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
						HSSFRow hssfRow1 = hssfSheet.getRow(rowNum);
						if (hssfRow1 != null) {
							String hql = "from SInformation as student where school_id = "+school_id;
							hql += " and student.student_name like '%"+getValue(hssfRow1.getCell(0))+"%'";
							List<SInformation> studentlist = informationDao.find(hql ,null,0,0);
							
							if(studentlist.size()>0) {
								Rewards rewards = new Rewards();
								rewards.setStudent_id(studentlist.get(0).getId());
								rewards.setDiscipline_name(getValue(hssfRow1.getCell(2)));
								rewards.setGrade(getValue(hssfRow1.getCell(3)));
								rewards.setRemarks(getValue(hssfRow1.getCell(4)));
								rewards.setUser_name(username);
								rewards.setStatus(0);
								rewards.setCreated_at(new Date());
								rewardsDao.save(rewards);
							}else {
								arrayList.add(getValue(hssfRow1.getCell(0)));
							}
							
						}
					}
				}        
			} catch (FileNotFoundException e) {
				e.printStackTrace();
				return R.error().put("data", arrayList);
			}
			return R.ok().put("data", "导入成功");
		}
		return R.error("文件不存在!");
	}
	
	/**
	 * 荣誉导入
	 * @param request
	 */
	@Transactional(readOnly = false)//
	@RequestMapping( value = "/honorToLead", method = RequestMethod.POST)
	public R honorToLead(@RequestParam("file") MultipartFile file) throws Exception {
		if(file.isEmpty()!=true){
			String token = tokenService.getSystemToken();
			Long school_id = JwtUtils.getSchoolId(token);
			String username = JwtUtils.getUsername(token);
			Schools schools = schoolsDao.findById(school_id);
			if(schools==null) {
				return R.error();
			}
			ArrayList<String> arrayList = new ArrayList<String>();
			InputStream stream = file.getInputStream();
			try {
				HSSFWorkbook hssfWorkbook = new HSSFWorkbook(stream);
				// 循环工作表Sheet         
				for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
					HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
					if (hssfSheet == null) {
						continue;
					}
					
					// 循环行Row
					for (int rowNum = 2; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
						HSSFRow hssfRow1 = hssfSheet.getRow(rowNum);
						if (hssfRow1 != null) {
							String hql = "from SInformation as student where school_id = "+school_id;
							hql += " and student.student_name like '%"+getValue(hssfRow1.getCell(0))+"%'";
							hql += " and student.student_learncode = "+getValue(hssfRow1.getCell(1))+"";
							List<SInformation> studentlist = informationDao.find(hql ,null,0,0);
							
							if(studentlist.size()>0) {
								Honor honor = new Honor();
								honor.setStudent_id(studentlist.get(0).getId());
								honor.setGain_time(getValue(hssfRow1.getCell(3)));
								honor.setExam_name(getValue(hssfRow1.getCell(2)));
								honor.setRanking(Integer.parseInt(getValue(hssfRow1.getCell(4))));
								honor.setGrade(getValue(hssfRow1.getCell(5)));
								honor.setUnits_name(getValue(hssfRow1.getCell(6)));
								honor.setStatus(0);
								honor.setRemarks(getValue(hssfRow1.getCell(7)));
								honor.setCreated_at(new Date());
								honor.setUser_name(username);
								honorDao.save(honor);
							}else {
								arrayList.add(getValue(hssfRow1.getCell(0)));
							}
							
						}
					}
				}        
			} catch (FileNotFoundException e) {
				e.printStackTrace();
				return R.error().put("data", arrayList);
			}
			return R.ok().put("data", "导入成功");
		}
		return R.error("文件不存在!");
	}
	@SuppressWarnings("static-access")     
	private String getValue(HSSFCell hssfCell) {
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
			// 返回布尔类型的值
			return String.valueOf(hssfCell.getBooleanCellValue());
		}else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
			// 返回数值类型的值                 
			return String.valueOf(hssfCell.getNumericCellValue());
		} else {
			// 返回字符串类型的值
			return String.valueOf(hssfCell.getStringCellValue());
		}         
	}

}

